package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MInOut;
import org.compiere.model.MInOutLine;
import org.compiere.model.MOrderLine;
import org.compiere.model.MOrg;
import org.compiere.model.MOrgInfo;
import org.compiere.model.MProduct;
import org.compiere.model.MUOM;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 *
 */
public class MM_Form_429 extends SvrProcess {
	//Form_429_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;
	int p_M_InOut_ID;
	/** */
	String file;

	/**
	 * Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++) {
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null)
				;
			else if (name.equals("M_InOut_ID")) 
				p_M_InOut_ID  = para[i].getParameterAsInt();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}

	}

	/* (non-Javadoc)
	 * @see org.compiere.process.SvrProcess#doIt()
	 */
	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "Form_429_.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		sheet.getRow(7).getCell(0).setCellValue(MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(14).getCell(51).setCellValue(new SimpleDateFormat("dd").format(new Timestamp(System.currentTimeMillis())));
		sheet.getRow(14).getCell(54).setCellValue(new SimpleDateFormat("MMMMM").format(new Timestamp(System.currentTimeMillis())));
		
		MInOut mInOut = new MInOut(m_ctx, p_M_InOut_ID, get_TrxName());
		sheet.getRow(17).getCell(46).setCellValue(mInOut.getDocumentNo());
		sheet.getRow(17).getCell(55).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(mInOut.getDateAcct()));
		
		MBPartner bPartner = new MBPartner(m_ctx, mInOut.getC_BPartner_ID(), get_TrxName());
		StringBuffer bpfullAdress = new StringBuffer();
		
		bpfullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getC_Country().getName() == null ? 
				"":", Страна "+mInOut.getC_BPartner_Location().getC_Location().getC_Country().getName());
		bpfullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getPostal() == null ? 
				"": ", "+mInOut.getC_BPartner_Location().getC_Location().getPostal());
		bpfullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getC_City().getName() == null ? 
				"":", город "+mInOut.getC_BPartner_Location().getC_Location().getC_City().getName());
		//fullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getC_Region().getName() == null ? 
		//"":", регион "+mInOut.getC_BPartner_Location().getC_Location().getC_Region().getName());
		bpfullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getAddress1() == null ? 
				"":", район "+mInOut.getC_BPartner_Location().getC_Location().getAddress1());
		bpfullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getAddress2() == null ? 
				"":", ул./просп./мкр "+mInOut.getC_BPartner_Location().getC_Location().getAddress2());
		bpfullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getAddress3() == null ? 
				"":", дом "+mInOut.getC_BPartner_Location().getC_Location().getAddress3());
		bpfullAdress.append(mInOut.getC_BPartner_Location().getC_Location().getAddress4() == null ? 
				"":", офис/квартира "+mInOut.getC_BPartner_Location().getC_Location().getAddress4());
		
		sheet.getRow(24).getCell(6).setCellValue(bPartner.getName2()+bpfullAdress);
		sheet.getRow(26).getCell(6).setCellValue(bPartner.getName2()+bpfullAdress);
		
		StringBuffer orgfullAdress = new StringBuffer();
		orgfullAdress.append(MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getC_Country().getName() == null ? 
				"":", Страна "+MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getC_Country().getName());
		orgfullAdress.append(MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getPostal() == null ? 
				"":", "+MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getPostal());
		orgfullAdress.append(MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getC_City().getName() == null ? 
				"":", город "+MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getC_City().getName());
		orgfullAdress.append(MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress1() == null ? 
				"":", "+MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress1());
		orgfullAdress.append(MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress2() == null ? 
				"":", "+MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress2());
		orgfullAdress.append(MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress3() == null ? 
				"":", "+MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress3());
		orgfullAdress.append(MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress4() == null ? 
				"":", "+MOrgInfo.get(m_ctx, Env.getAD_Org_ID(m_ctx), get_TrxName()).getC_Location().getAddress4());
		sheet.getRow(28).getCell(6).setCellValue(MOrg.get(m_ctx,Env.getAD_Org_ID(m_ctx)).getName()+orgfullAdress);
		
		sheet.getRow(30).getCell(33).setCellValue(" "+new SimpleDateFormat("dd.MM.yyyy").format(mInOut.getMovementDate()));
		R = sheet.getRow(31);
		R.getCell(5).setCellValue(mInOut.getC_Order().getCMS_Contract().getDocumentNo());
		R.getCell(11).setCellValue(new SimpleDateFormat("dd").format(mInOut.getC_Order().getCMS_Contract().getCMS_Contract_CreatDate() == null ?
				new Timestamp(System.currentTimeMillis()): mInOut.getC_Order().getCMS_Contract().getCMS_Contract_CreatDate()));
		R.getCell(14).setCellValue(new SimpleDateFormat("MMMMM").format(mInOut.getC_Order().getCMS_Contract().getCMS_Contract_CreatDate() == null ?
				new Timestamp(System.currentTimeMillis()): mInOut.getC_Order().getCMS_Contract().getCMS_Contract_CreatDate()) 
				+ " "+ new SimpleDateFormat("yyyy").format(mInOut.getC_Order().getCMS_Contract().getCMS_Contract_CreatDate() == null ?
						new Timestamp(System.currentTimeMillis()): mInOut.getC_Order().getCMS_Contract().getCMS_Contract_CreatDate()) );
// there is must be m_inoutline cycle, but this cycle down, because dynamic row count!
		R = sheet.getRow(41);
		R.getCell(0).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(mInOut.getDateReceived() == null ? new Timestamp(System.currentTimeMillis()):mInOut.getDateReceived()));
		R.getCell(45).setCellValue(new SimpleDateFormat("dd.MM.yyyy").format(mInOut.getDateReceived() == null ? new Timestamp(System.currentTimeMillis()):mInOut.getDateReceived()));
//m_inoutline cycle		
		String sql = "SELECT M_InOutLine_ID FROM M_InOutLine WHERE M_InOut_ID = "+p_M_InOut_ID;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int lineCount = 0;
		try{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int row = 36;
			while(rs.next()){
				row++; lineCount++;
				sheet.shiftRows(row, row+75, 1);
				MInOutLine iMInOutLine = new MInOutLine(m_ctx, rs.getInt(1), get_TrxName());
				R = sheet.getRow(row);
				//set row height because different height of rows
				R.setHeight(sheet.getRow(16).getHeight());
				//Cycle cells style for row
				for(int i = 0; i < 63; i++)
				{
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(16).getCell(46).getCellStyle());
				}
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 7)); //col1
				sheet.addMergedRegion(new CellRangeAddress(row, row, 8, 15)); //col2
				sheet.addMergedRegion(new CellRangeAddress(row, row, 16, 23)); //col3
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 38)); //col4
				R.getCell(24).setCellValue(iMInOutLine.getM_Product().getName());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 39, 46)); //col5
				R.getCell(39).setCellValue(bPartner.getName2());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 47, 54)); 	//col6
				sheet.addMergedRegion(new CellRangeAddress(row, row, 55, 62)); 	//col7
			}
		}
		catch(Exception e){
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		//sheet.getRow(67+lineCount).setHeight(sheet.getRow(16).getHeight());
//--Second cycle		
		try{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int row = 63 + lineCount, tempLineCount=lineCount;
			while(rs.next()){
				row++; lineCount++;
				sheet.shiftRows(row, row+75+tempLineCount, 1);
				MInOutLine iMInOutLine = new MInOutLine(m_ctx, rs.getInt(1), get_TrxName());
				R = sheet.getRow(row);
				R.setHeight(sheet.getRow(16).getHeight());
				//Cycle style for row
				for(int i = 0; i < 60; i++)
				{
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(16).getCell(46).getCellStyle());
				}
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 9)); //col1
				sheet.addMergedRegion(new CellRangeAddress(row, row, 10, 19)); //col2
				R.getCell(10).setCellValue(mInOut.getM_Warehouse().getName());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 20, 29)); //col3
				sheet.addMergedRegion(new CellRangeAddress(row, row, 30, 39)); //col4
				//R.getCell(30).setCellValue(new X_M_Product_Acct(m_ctx, iMInOutLine.getM_Product_ID(), get_TrxName()).getP_Asset_Acct());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 49)); //col5
				sheet.addMergedRegion(new CellRangeAddress(row, row, 50, 59)); //col6
			}
		}
		catch(Exception e){
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		//--third cycle		
		try{
			pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = pstmt.executeQuery();
			int row = 67+lineCount, tempLineCount=lineCount;
			while(rs.next()){
				row++; lineCount++;
				sheet.shiftRows(row, row+75+tempLineCount, 1);
				MInOutLine iMInOutLine = new MInOutLine(m_ctx, rs.getInt(1), get_TrxName());
				R = sheet.getRow(row);
				R.setHeight(sheet.getRow(16).getHeight());
				//Cycle style for row2
				for(int i = 0; i < 65; i++)
				{
					C = R.createCell(i);
					C.setCellStyle(sheet.getRow(16).getCell(46).getCellStyle());
				}
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 3)); //col1
				sheet.addMergedRegion(new CellRangeAddress(row, row, 4, 7)); //col2
				R.getCell(4).setCellValue(new MProduct(m_ctx, iMInOutLine.getM_Product_ID(), get_TrxName()).getValue());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 8, 10)); //col3
				R.getCell(8).setCellValue(new MUOM(m_ctx, iMInOutLine.getM_Product().getC_UOM_ID(), get_TrxName()).getName());
				sheet.addMergedRegion(new CellRangeAddress(row, row, 11, 13)); //col4
				sheet.addMergedRegion(new CellRangeAddress(row, row, 14, 16)); //col5
				R.getCell(14).setCellValue(String.valueOf(iMInOutLine.getQtyEntered()));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 17, 19)); //col6
				R.getCell(17).setCellValue(String.valueOf(new MOrderLine(m_ctx, iMInOutLine.getC_OrderLine_ID(), get_TrxName()).getPriceEntered()));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 20, 23)); //col7
				R.getCell(20).setCellValue(String.valueOf(new MOrderLine(m_ctx, iMInOutLine.getC_OrderLine_ID(), get_TrxName()).getLineNetAmt()));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 26)); //col8
				sheet.addMergedRegion(new CellRangeAddress(row, row, 27, 29)); //col9
				R.getCell(27).setCellValue(String.valueOf(iMInOutLine.getQtyEntered()));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 30, 33)); //col10
				R.getCell(30).setCellValue(String.valueOf(new MOrderLine(m_ctx, iMInOutLine.getC_OrderLine_ID(), get_TrxName()).getPriceEntered()));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 34, 37)); //col11
				R.getCell(34).setCellValue(String.valueOf(new MOrderLine(m_ctx, iMInOutLine.getC_OrderLine_ID(), get_TrxName()).getLineNetAmt()));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 38, 40)); //col12
				sheet.addMergedRegion(new CellRangeAddress(row, row, 41, 43)); //col13
				sheet.addMergedRegion(new CellRangeAddress(row, row, 44, 47)); //col14
				sheet.addMergedRegion(new CellRangeAddress(row, row, 48, 50)); //col15
				sheet.addMergedRegion(new CellRangeAddress(row, row, 51, 54)); //col16
				sheet.addMergedRegion(new CellRangeAddress(row, row, 55, 57)); //col17
				sheet.addMergedRegion(new CellRangeAddress(row, row, 58, 61)); //col18
				sheet.addMergedRegion(new CellRangeAddress(row, row, 62, 64)); //col19
			}
			
		}
		catch(Exception e){
			log.log(Level.SEVERE, sql.toString(), e);
		}
		finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
					Util.launchFile(new File(file));		
		return Msg.translate(m_ctx, "Success");
	}

}
